#!/bin/bash
echo $(date "+%Y-%m-%d %H:%M:%S")
TIME=$(date "+%Y%m%d%H")
DATE=$(date "+%Y%m%d")
path="/home/tjhk/server/cron/csv/$DATE/"
if test -e $path
then
    echo '$path文件已存在!'
else
    eval "mkdir -p $path"
fi
# 更新cid
docker exec postgis /bin/bash -c 'psql -U tjhk -d cyc12 -c "UPDATE location_now SET c_id = g.c_id FROM region g WHERE ST_Within(location_now.location,g.geom) AND location_now.c_id IS NULL"'
# 汇总分区车辆
docker exec postgis /bin/bash -c 'psql -U tjhk -d cyc12 -c "INSERT INTO county_vehicle_count(c_id,fuel_type,standard_emission,count_num,hour_time) SELECT c_id,fuel_type,standard_emission,count(*) AS count_num,to_char(now()+ interval '\''7 h'\'', '\''yyyy-mm-dd hh24'\'') AS hour_time FROM location_now WHERE to_char(collect_time, '\''yyyy-mm-dd hh24'\'') = to_char(now() + interval '\''7 h'\'', '\''yyyy-mm-dd hh24'\'') GROUP BY c_id,fuel_type,standard_emission"'
# 输出csv
docker exec postgis /bin/bash -c 'psql -U tjhk -d cyc12 -c "SELECT plate_no,speed,angle, altitude, mileage, st_astext(location, 4326) as location, c_id, grid_id, collect_time, standard_emission, fuel_type, vehicle_type_ton, vehicle_native, is_heavy_truck, is_ton_10, is_ton_10_20, is_ton_20_30, is_ton_30_40, is_ton_40, output_volume, acc_status FROM location_now where to_char(collect_time, '\''yyyy-mm-dd hh24'\'') = to_char(now() + interval '\''7 h'\'', '\''yyyy-mm-dd hh24'\'');"  -A -F, > test.csv'
docker cp postgis:/test.csv ./
#mv test.csv /home/tjhk/server/cron/csv/"$TIME".csv
eval "mv test.csv $path$TIME.csv"
echo "生成文件$TIME.csv成功！"
docker exec postgis /bin/bash -c 'psql -U tjhk -d cyc12 -c "DELETE FROM location_now WHERE to_char(collect_time, '\''yyyy-mm-dd hh24'\'') = to_char(now() + interval '\''7 h'\'', '\''yyyy-mm-dd hh24'\'')"'

